//
var Client = require('mysql').Client,
    client = new Client(),
    TEST_DATABASE = 'rmb48_test',
    
    COLLECTION = 'Collection';
    ELEMENT = 'Element';
    SKE48 = 'Ske48';
    CONDITION = 'Conditions';
	
	COL_PROGRESS = 'CollectionProgress';
	ELE_PROGRESS = 'ElementProgress';
	CON_PROGRESS = 'ConditionProgress';
	
	USER = "User";

client.user = 'db_user';
client.password = 'db_user';

exports.DBController = function(){};

//コレクション一覧を取得
exports.DBController.prototype.get_collection = function( user_id, callback){
	client.connect();

	//DB選択
	client.query('USE '+TEST_DATABASE);

	//テーブル作成
	client.query(
	  'SELECT * FROM '+ COLLECTION + ' INNER JOIN ' + COL_PROGRESS + ' ON '
		+ COLLECTION + '.id = ' + COL_PROGRESS + '.collection_id AND '
		+ COL_PROGRESS + '.user_id = (select ' + USER + '.id from ' 
		+ USER + ' where ' + USER + '.twitter_id = ?)'
	,[ user_id]
	,  function selectCb(err, results, fields) {
	    if (err) {
	      throw err;
	    }
	    client.end();
	  	callback( results);
	  }
	);


}

//エレメント一覧を取得
exports.DBController.prototype.get_element = function( user_id, collection_id, callback){

	client.connect();
	//DB選択
	client.query('USE '+TEST_DATABASE);

	//テーブル作成
	client.query(
	  'SELECT * FROM '+ ELEMENT + ' INNER JOIN ' + ELE_PROGRESS + ' ON '
		+ ELEMENT + '.id = ' + ELE_PROGRESS + '.element_id AND '
		+ ELE_PROGRESS + '.user_id = (select ' + USER + '.id from ' 
		+ USER + ' where ' + USER + '.twitter_id = ?)'
	,[ user_id],
	  function selectCb(err, results, fields) {
	    if (err) {
	      throw err;
	    }
	    client.end();
	  	callback(results);
	  }
	);
}

//SKE48一覧を取得（詳細なプロフィール含む）
exports.DBController.prototype.get_ske48 = function( user_id, collection_id, callback){
	
	client.connect();

	//DB選択
	client.query('USE '+TEST_DATABASE);

	//テーブル作成
	client.query(
	  'SELECT * FROM '+ ELEMENT + ' INNER JOIN ' + SKE48 + ' ON ' + ELEMENT + '.id = '+ SKE48 + '.id '
		+ ' INNER JOIN ' + ELE_PROGRESS + ' ON '
		+ ELEMENT + '.id = ' + ELE_PROGRESS + '.element_id AND '
		+ ELE_PROGRESS + '.user_id = (select ' + USER + '.id from ' 
		+ USER + ' where ' + USER + '.twitter_id = ?)'
	,[ user_id]
	,  function selectCb(err, results, fields) {
	    if (err) {
	      throw err;
	    }
	    client.end();
	  	callback(results);
	  }
	);
}


//SKE48のメンバー一人の詳細なプロフィールを取得
exports.DBController.prototype.get_ske48_detail = function( user_id, collection_id, element_id, callback){
	client.connect();

	//DB選択
	client.query('USE '+TEST_DATABASE);

	//テーブル作成
		client.query(
	  'SELECT * FROM '+ ELEMENT + ' INNER JOIN ' + SKE48 + ' ON ' + ELEMENT + '.id = '+ SKE48 + '.id '
		+ ' INNER JOIN ' + ELE_PROGRESS + ' ON '
		+ ELEMENT + '.id = ' + ELE_PROGRESS + '.element_id AND '
		+ ELE_PROGRESS + '.user_id = (select ' + USER + '.id from ' 
		+ USER + ' where ' + USER + '.twitter_id = ?) AND ' + ELEMENT + '.id = ?'
	,[ user_id, element_id]
	,  function selectCb(err, results, fields) {
	    if (err) {
	      throw err;
	    }
	    client.end();
	  	callback(results);
	  }
	);
}


//条件一覧を取得 第2引数にパラメータ{id, keywords}、
exports.DBController.prototype.match_containText = function(  param, callback){
	client.connect();

	//DB選択
	client.query('USE '+TEST_DATABASE);

	//テーブル作成
	client.query(
	  'SELECT * FROM '+ CONDITION + ' WHERE '+ CONDITION + '.method = \'containText\'',
	  function selectCb(err, results, fields) {
	    if (err) {
	      throw err;
	    }
	   	for( var i=0; i < param.keywords.length; i++){
	  		containTextDbSke48( param.keywords[i], results, callback);
	  	}
	  		
	  	
	  }
	);
	
		setTimeout(function(){client.end();}, 7000);
	//メソッド内メソッド　条件の引数からカラム一つずつ探索。検索に引っかかった詳細プロフィールを返す　遅い
	function containTextDbSke48(word, results, callback){
		for( var i=0; i<results.length; i++){
			var sql1 =  results[i].arg1 + ' LIKE \'%' + word + '%\'';
			
			var sql2 = '';
			if( sql2 != null && sql2 != ""){
				sql2 = ' OR ' + results[i].arg2 + ' LIKE \'%' + word + '%\'';
			}
			
			var sql3 = ''
			if( sql3 != null && sql3 != ""){
				sql3 = ' OR ' + results[i].arg3 + ' LIKE \'%' + word + '%\'';
			}
			
			client.query(
				'SELECT * FROM ' + CONDITION + ' WHERE ' + CONDITION + '.id = '
				+ results[i].id + ' AND ' + CONDITION + '.element_id = '
				+ '(SELECT '+ SKE48 + '.id FROM ' + SKE48 + ' WHERE ' + SKE48 + '.id = '
				+ results[i].element_id 
				+ ' AND (' + sql1 + sql2 + sql3 + '))'
				, function selectCb(err, results, fields) {
				    if (err) {
				      throw err;
				    }
					updateCondition( results, callback);
					//callback(results);
			  	}
			);
		}
//		client.end();
	}
			
	function updateCondition( results2, callback){
		
		d = new Date();
		dd = (1900+d.getYear()) + '/' +  (1+d.getMonth()) + '/' +  d.getDate() + ' ' + 
			d.getHours() + '/' +  d.getMinutes() + '/' +  d.getSeconds();
		//results[0].id が ConditionProgresssのcondition_id
		
		if( results2.length > 0){

		  var sql = 'UPDATE '+ CON_PROGRESS +' '+
			'SET flag = ?, updated = ? WHERE condition_id = ? AND '
			+ CON_PROGRESS + '.user_id =  (SELECT ' 
			+ USER + '.id FROM ' + USER + ' WHERE ' + USER+ '.twitter_id = ?) ';
		//console.log(sql);
			var query = client.query( sql, 
				[ '1', dd
				, results2[0]['id']
				 , param.id ]
		);
			
		var sql2 = 'update ' + ELE_PROGRESS + ' set updated = ?, now_point = ( select COUNT(*) from ' + CON_PROGRESS + ' where user_id =  (SELECT ' 	+ USER + '.id FROM ' + USER + ' WHERE ' + USER+ '.twitter_id = ?) and flag = ? and condition_id in (select id from ' + CONDITION + ' where element_id = ?)), max_point = ( select COUNT(*) from ' + CON_PROGRESS + ' where ' + CON_PROGRESS + '.user_id =  (SELECT ' 	+ USER + '.id FROM ' + USER + ' WHERE ' + USER+ '.twitter_id = ?)  and condition_id in (select id from ' + CONDITION + '  where element_id = ?)) where ' + ELE_PROGRESS + '.element_id = ? and ' + ELE_PROGRESS + '.user_id =  (SELECT ' 	+ USER + '.id FROM ' + USER + ' WHERE ' + USER+ '.twitter_id = ?) ';
			var query2 = client.query( sql2,
				[ dd, param.id, 1, results2[0]['element_id'], param.id, results2[0]['element_id'], results2[0]['element_id'], param.id]
			);

		var sql3 = 'update ' + COL_PROGRESS + ' set updated = ?, now_point = ( select COUNT(*) from ' + ELE_PROGRESS + ' where user_id =  (SELECT ' 	+ USER + '.id FROM ' + USER + ' WHERE ' + USER+ '.twitter_id = ?) and  clear_point <= now_point and element_id in (select id from ' + ELEMENT + ' where collection_id = ?)), max_point = ( select COUNT(*) from ' + ELE_PROGRESS + ' where ' + ELE_PROGRESS + '.user_id =  (SELECT ' 	+ USER + '.id FROM ' + USER + ' WHERE ' + USER+ '.twitter_id = ?)  and element_id in (select id from ' + ELEMENT + '  where collection_id = ?)) where ' + COL_PROGRESS + '.collection_id = ? and ' + COL_PROGRESS + '.user_id =  (SELECT ' 	+ USER + '.id FROM ' + USER + ' WHERE ' + USER+ '.twitter_id = ?) ';
			var col_id = 1;
			var query3 = client.query( sql3,
				[ dd, param.id, col_id, param.id,  col_id,  col_id, param.id]
			);
			
			
		callback(results2);
		}
		
	}

	//メソッド内メソッド SKE48　カラム固定メソッド探索　速度が速い
	function containTextDbSke48Fixed(word, results, callback){
		client.query(
			'SELECT * FROM ' + SKE48 + ' WHERE hobby LIKE \'%' + word + '%\''
			+ ' OR food LIKE \'%' + word + '%\'' + ' OR message LIKE \'%' + word + '%\''
			, function selectCb(err, results, fields) {
			    if (err) {
			      throw err;
			    }
			    client.end();
				callback(results)
		  	}
		);
	}	
}
//update ElementProgress set now_point = ( select COUNT(*) from ConditionProgress where user_id = 1 and flag = 1 and condition_id in (select id from Conditions where element_id = 57)), max_point = ( select COUNT(*) from ConditionProgress where ConditionProgress.user_id = 1  and condition_id in (select id from Conditions where element_id = 57)) where ElementProgress.element_id = 57 and ElementProgress.user_id = 1;

//update CollectionProgress set now_point = ( select COUNT(*) from ElementProgress where user_id = 1 and clear_point <= now_point and element_id in (select id from Element where collection_id = 1)), max_point = ( select COUNT(*) from ElementProgress where ElementProgress.user_id = 1 and element_id in (select id from Element where collection_id = 1)) where CollectionProgress.collection_id = 1 and CollectionProgress.user_id = 1;


//var dbc = new exports.DBController();
//dbc.match_containText( {id:"g_m_k", keywords:['オムライス','アイドル']}, function(r){if(r.length>0){console.log(r)}} );
//dbc.get_ske48_detail( "g_m_k", 1, 5, function(r){if(r.length>0){console.log(r)}} );
